import sqlite3
import hashlib
from utils import *
from logger import GLOBAL_LOGGER as LOG


class DBhelper:
    def __init__(self, db_file):
        self.db_file = db_file
        self.conn = None
        self.cursor = None
        self.connect()
        self.create_table()

    def connect(self):
        self.conn = sqlite3.connect(self.db_file)
        self.cursor = self.conn.cursor()

    def disconnect(self):
        if self.conn:
            self.conn.close()

    def create_table(self):
        self.cursor.execute('''
            CREATE TABLE IF NOT EXISTS query_results (
                query_hash TEXT PRIMARY KEY,
                query TEXT,
                result TEXT
            )
        ''')
        self.conn.commit()

    def hash_query(self, query):
        return hashlib.md5(query.encode()).hexdigest()

    def execute_query(self, query):
        query_hash = self.hash_query(query)
        self.cursor.execute('SELECT query, result FROM query_results WHERE query_hash = ?', (query_hash, ))
        existing_result = self.cursor.fetchone()
        if existing_result:
            return existing_result[1]

    def insert_query(self, query, result):
        query_hash = self.hash_query(query)
        existing_result = self.execute_query(query)
        if existing_result:
            return
        self.cursor.execute('INSERT INTO query_results (query_hash, query, result) VALUES (?, ?, ?)', (query_hash, query, result))
        self.conn.commit()

    def update_query(self, query, new_result):
        query_hash = self.hash_query(query)
        self.cursor.execute('UPDATE query_results SET result = ? WHERE query_hash = ?', (new_result, query_hash))
        self.conn.commit()

    def delete_query(self, query):
        query_hash = self.hash_query(query)
        self.cursor.execute('DELETE FROM query_results WHERE query_hash = ?', (query_hash,))
        self.conn.commit()

    def delete_hash(self, hash):
        self.cursor.execute('DELETE FROM query_results WHERE query_hash = ?', (hash,))
        self.conn.commit()

    def clear_database(self):
        self.cursor.execute('DELETE FROM query_results')
        self.conn.commit()
        
    def fetch_all_entries(self):
        self.cursor.execute('SELECT * FROM query_results')
        entries = self.cursor.fetchall()
        return entries
    
    # 清理和querys results不对应的所有记录
    def clean_db(self,querys,resposes):
        LEN_QUERYS = len(querys)
        entries = self.fetch_all_entries()
        for item in entries:
            hash,db_querys,db_resposes = item[0],tuple_str2list(item[1]), str2list(item[2])
            if len(db_querys) >= LEN_QUERYS and db_querys[:LEN_QUERYS] == querys and db_resposes[:LEN_QUERYS] != resposes:
                LOG.logger.warning(f"clean item:{db_querys}:{db_resposes}")
                self.delete_hash(hash)